import pymysql
from flask import Flask, jsonify, request, flash
from flask import render_template
from pymysql.cursors import DictCursor



app = Flask(__name__)
app.config['SECRET_KEY'] = '#8asdftt7'


def get_db_connection():
    connection = pymysql.connect(
        host='localhost',
        user='root',
        password='123456',
        database='flask',
        cursorclass=DictCursor  # 指定游标类型
    )
    return connection


# 首页
@app.route('/stock')
def index():
    return render_template('stock.html')

@app.route('/next')
def next():
    return render_template('stock_analysis_fixed.html')

# 获取所有股票数据
@app.route('/stocks/find', methods=['POST'])
def find_stocks():
    connection = get_db_connection()
    try:
        data = request.get_json() or {}
        currentPage = int(request.args.get('currentPage', 1))
        pageSize = int(request.args.get('pageSize', 10))
        begin = (currentPage - 1) * pageSize

        # 构建查询条件
        name = data.get('name', '')
        conditions = []
        params = []

        if name:
            conditions.append("name LIKE %s")
            params.append(f"%{name}%")

        where_clause = "WHERE " + " AND ".join(conditions) if conditions else ""

        # 查询股票数据
        query = f"SELECT * FROM stock_data {where_clause} LIMIT %s, %s"
        params.extend([begin, pageSize])

        with connection.cursor() as cursor:
            cursor.execute(query, params)
            rows = cursor.fetchall()

            # 查询总记录数
            count_query = f"SELECT COUNT(*) as count FROM stock_data {where_clause}"
            cursor.execute(count_query, params[:-2])  # 移除分页参数
            total_count = cursor.fetchone()['count']

        return jsonify({
            'rows': rows,
            'totalCount': total_count
        })
    finally:
        connection.close()


# 添加股票
@app.route('/stocks/add', methods=['POST'])
def add_stock():
    connection = get_db_connection()
    try:
        data = request.get_json()

        # 检查股票是否已存在
        with connection.cursor() as cursor:
            cursor.execute('SELECT * FROM stock_data WHERE name = %s', (data['name'],))
            stock = cursor.fetchone()

            if stock:
                return jsonify({'success': False, 'message': '股票已存在'})

            # 处理市值单位
            market_cap = data['market_cap']
            if isinstance(market_cap, str):
                if '亿' in market_cap:
                    market_cap = float(market_cap.replace('亿', '')) * 1e8
                elif '万' in market_cap:
                    market_cap = float(market_cap.replace('万', '')) * 1e4
                else:
                    market_cap = float(market_cap)

            # 插入股票数据
            cursor.execute(
                '''INSERT INTO stock_data 
                   (name, latest_price, price_change, change_percentage, 
                    opening_price, highest_price, lowest_price, previous_close, market_cap, pe_ratio)
                   VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)''',
                (data['name'], data['latest_price'], data['price_change'],
                 data['change_percentage'], data['opening_price'],
                 data['highest_price'], data['lowest_price'],
                 data['previous_close'], market_cap, data['pe_ratio'])
            )
            connection.commit()

        return jsonify({'success': True, 'message': '添加成功'})
    except Exception as e:
        connection.rollback()
        return jsonify({'success': False, 'message': str(e)}), 500
    finally:
        connection.close()



# 修改股票
@app.route('/stocks/update', methods=['POST'])
def update_stock():
    connection = get_db_connection()
    try:
        stock_id = request.args.get('id')
        data = request.get_json()

        with connection.cursor() as cursor:
            # 检查股票是否存在
            cursor.execute('SELECT * FROM stock_data WHERE id = %s', (stock_id,))
            stock = cursor.fetchone()

            if not stock:
                return jsonify({'success': False, 'message': '股票不存在'})

            # 处理市值单位
            market_cap = data.get('market_cap', stock['market_cap'])
            if isinstance(market_cap, str):
                if '亿' in market_cap:
                    market_cap = float(market_cap.replace('亿', '')) * 1e8
                elif '万' in market_cap:
                    market_cap = float(market_cap.replace('万', '')) * 1e4
                else:
                    market_cap = float(market_cap)

            # 更新股票数据
            update_query = '''
                UPDATE stock_data 
                SET name = %s, latest_price = %s, price_change = %s, 
                    change_percentage = %s, opening_price = %s, 
                    highest_price = %s, lowest_price = %s, 
                    previous_close = %s, market_cap = %s, pe_ratio = %s
                WHERE id = %s
            '''
            cursor.execute(
                update_query,
                (data.get('name', stock['name']),
                 data.get('latest_price', stock['latest_price']),
                 data.get('price_change', stock['price_change']),
                 data.get('change_percentage', stock['change_percentage']),
                 data.get('opening_price', stock['opening_price']),
                 data.get('highest_price', stock['highest_price']),
                 data.get('lowest_price', stock['lowest_price']),
                 data.get('previous_close', stock['previous_close']),
                 market_cap,
                 data.get('pe_ratio', stock['pe_ratio']),
                 stock_id)
            )
            connection.commit()

        return jsonify({'success': True, 'message': '修改成功'})
    except Exception as e:
        connection.rollback()
        return jsonify({'success': False, 'message': str(e)}), 500
    finally:
        connection.close()


# 删除单个股票
@app.route('/stocks/deleteById', methods=['GET'])
def delete_stock():
    connection = get_db_connection()
    try:
        stock_id = request.args.get('id')

        with connection.cursor() as cursor:
            # 检查股票是否存在
            cursor.execute('SELECT * FROM stock_data WHERE id = %s', (stock_id,))
            stock = cursor.fetchone()

            if not stock:
                return 'fail'

            # 删除股票
            cursor.execute('DELETE FROM stock_data WHERE id = %s', (stock_id,))
            connection.commit()

        return 'success'
    except Exception as e:
        connection.rollback()
        return 'fail'
    finally:
        connection.close()


# 批量删除股票
@app.route('/stocks/deleteMany', methods=['POST'])
def delete_many_stocks():
    connection = get_db_connection()
    try:
        data = request.get_json()

        if not data:
            return 'fail'

        with connection.cursor() as cursor:
            # 构建批量删除语句
            placeholders = ', '.join(['%s'] * len(data))
            query = f'DELETE FROM stock_data WHERE id IN ({placeholders})'
            cursor.execute(query, data)
            connection.commit()

        return 'success'
    except Exception as e:
        connection.rollback()
        return 'fail'
    finally:
        connection.close()


if __name__ == '__main__':
    app.run(host='0.0.0.0', port=8080, debug=True)